/**
 * All Rights Reserved , Copyright (C) 2020 , 青岛鼎信通讯股份有限公司
 * <p>
 * MpmProjectController
 * 项目立项
 * <p>
 * 修改纪录
 * 2020-12-25 版本：1.0 lanjianwei 创建。
 *
 * @version 版本：1.0
 * @author 作者：lanjianwei
 * 创建日期：2020-12-25
 */

package com.topscomm.mpm.controller;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.topscomm.basic.util.SQLAdapter;
import com.topscomm.main.hessian.IMmsDeptExtraInfoService;
import com.topscomm.mpm.MpmSystemConst;
import com.topscomm.mpm.pojo.MpmProjectTaskEntity;
import com.topscomm.mpm.utils.ConvertTimeUtil;
import com.topscomm.mpm.vo.MpmProjectTaskDetailsVO;
import com.topscomm.pub.vo.Query;
import com.topscomm.tap.ITapService;
import com.topscomm.tap.common.DatacodeCache;
import com.topscomm.tap.common.TapSystemConst;
import com.topscomm.mpm.pojo.MpmProjectSaleEntity;
import com.topscomm.tap.security.SecurityUserDetails;
import com.topscomm.mpm.util.OfficeOperateUtilExpand;
import java.math.RoundingMode;
import java.rmi.server.ExportException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.topscomm.basic.exception.BusinessException;
import com.topscomm.basic.exception.MessageException;
import com.topscomm.basic.exception.ServiceException;
import com.topscomm.cbo.pojo.CboDeptEntity;
import com.topscomm.cbo.service.ICboDeptService;
import com.topscomm.main.hessian.IMmsProvinceCityMatchinfoService;
import com.topscomm.mis.pojo.MisDeptArchEntity;
import com.topscomm.mis.service.IMisDeptArchService;
import com.topscomm.mpm.pojo.MpmProjectEntity;
import com.topscomm.mpm.service.impl.MpmProjectPolymerizationService;
import com.topscomm.mpm.service.impl.MpmProjectService;
import com.topscomm.pub.util.*;
import com.topscomm.pub.vo.Page;
import com.topscomm.pub.vo.ResponseResult;
import com.topscomm.tap.common.ParameterCache;
import com.topscomm.tap.common.SessionUserBean;
import com.topscomm.tap.threadlocal.ThreadLocalContext;
import com.topscomm.tap.util.ErrorLogUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.codehaus.jackson.map.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: lanjianwei
 * @date: 2020-12-28
 * @description:项目立项聚合附加表Controller
 * @modify:
 */
@RestController
@RequestMapping("mpm/projectpolymerization")
public class MpmProjectPolymerizationController  {
    private Logger logger = LoggerFactory.getLogger(MpmProjectPolymerizationController.class);
    @Resource(name = "mpmProjectController")
    private MpmProjectController mpmProjectController;
    @Resource(name = "mpmProjectService")
    private MpmProjectService mpmProjectService;
    @Resource(name = "mpmProjectPolymerizationService")
    private MpmProjectPolymerizationService mpmProjectPolymerizationService;
    @Autowired
    private IMisDeptArchService misDeptArchService;
    @Autowired
    private ICboDeptService cboDeptService;
    @Autowired
    private IMmsProvinceCityMatchinfoService mmsProvinceCityMatchinfoService;
    @Qualifier("securityUserDetailsService")
    private UserDetailsService userDetailsService;
    @Autowired
    private DatacodeCache datacodeCache;
    @Autowired
    private IMmsDeptExtraInfoService mmsDeptExtraInfoService;

    private static final String NULL = "null";

    @GetMapping(value = "/dataCodeList")
    public ResponseResult<Object> dataCodeList(@RequestParam(required = false) Map<String, Object> paraMap) {
        ResponseResult<Object>  responseResult= mpmProjectPolymerizationService.datacodeList(paraMap);
        return responseResult;
    }

    /**
     * @Title: insert
     * @description:聚合拆分，分表插入
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月28日下午6:08:08
     * @modify:
     */
    @PostMapping(value = "/insert")
    public ResponseResult<Object> insert(@RequestBody(required = false) Map<String, Object> paraMap) {
        try {
            Map<String, Object> resultMap = mpmProjectPolymerizationService.insert(paraMap);
            return ResponseResult.ok(resultMap);
        }catch (Exception ex) {
			ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
        	return ResponseResult.error(ex.getMessage());
		}
    }


   /**
   * @author liuzhenfei
   * @Description 立项变更保存
   * @Date 2021/7/3 10:48
   * @Param [paraMap]
   * @return com.topscomm.pub.vo.ResponseResult<java.lang.Object>
   **/
    @PostMapping(value = "/project/change/insert")
    public ResponseResult<Object> insertForProjectChange(@RequestBody(required = false) Map<String, Object> paraMap) {
        try {
            return mpmProjectPolymerizationService.insertForProjectChange(paraMap);
        }catch (MessageException ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error(ex.getMessage());
        }catch (Exception ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("插入字段出错，请联系管理员!");
        }
    }


    /**
     * @Title: update
     * @description:更新
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午2:00:30
     * @modify:
     */
    @PutMapping(value = "/update")
    public ResponseResult<Object> update(@RequestBody(required = false) Map<String, Object> paraMap) {
        try {
            Map<String, Object> res = mpmProjectPolymerizationService.update(paraMap);
            return ResponseResult.ok(res);
        } catch (Exception ex) {
			ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("更新字段出错，请联系管理员!");
        }
    }


    /**
     * @Title: load
     * @description:load加载单据
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午2:00:59
     * @modify:
     */
    @GetMapping(value = "/load")
    public ResponseResult<Object> load(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            Map<String, Object> resultMap = mpmProjectPolymerizationService.load(paraMap);
            return ResponseResult.ok(resultMap);
        } catch (Exception ex) {
			ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("加载单据页面出错，请联系管理员!");
        }
    }

    /**
     * @Title: listprojectQueryList
     * @description:列表list
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月29日下午3:49:30
     * @modify:
     */
    @GetMapping(value = "/list")
    public ResponseResult<Object> list(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            SessionUserBean userBean = mpmProjectController.getUserBean();
            Page page = mpmProjectPolymerizationService.list(paraMap, userBean);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
			ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("加载列表页面出错，请联系管理员!");
        }
    }

    @GetMapping(value = "/getProvinceCityList")
    public ResponseResult<Object> getProvinceCityList(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            // 获取省区编码并赋值
            List<Map<String, Object>> deptCommonCodeList = this.mpmProjectService.queryMap(paraMap, "getDeptCommonCode");
            if (!deptCommonCodeList.isEmpty()) {
                paraMap.put("deptcommomcode", deptCommonCodeList.get(0).get("deptcommoncode"));
            }
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("provinceCityList", this.mmsProvinceCityMatchinfoService.acquireCascaderAllLowerArea(paraMap));
            return ResponseResult.ok(resultMap);
        } catch (Exception ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("获取省市县信息失败，请联系管理员!");
        }
    }

    /**
     * @author: wudaliang
     * @date: 2021-03-30
     * @description: 修改项目级别变更历史字段
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     * @modify:
     */
    @PostMapping(value = "/updateLevelHistory")
    public ResponseResult<Object> updateLevelHistory(@RequestBody(required = false) Map<String, Object> paraMap) {
        try {
            this.mpmProjectService.updateLevelHistory(paraMap);
            return ResponseResult.ok("成功");
        }catch (Exception e){
            throw new MessageException("修改‘项目级别变更历史’字段失败，请联系管理员");
        }
    }


    /**
     * @author: wudaliang
     * @date: 2021-03-12
     * @param: paraMap
     * @description: 项目追踪页面
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     * @modify:
     */
    @GetMapping(value = "/listForTrack")
    public ResponseResult<Object> listForTrack(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            SessionUserBean userBean = mpmProjectController.getUserBean();
            Page page = mpmProjectPolymerizationService.listForTrack(paraMap, userBean);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("加载列表页面出错，请联系管理员!");
        }
    }


    /**
     * @Title: delete
     * @description:删除
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月30日下午2:11:07
     * @modify:
     */
    @DeleteMapping(value = "/delete")
    public ResponseResult<Object> delete(@RequestBody Map<String, Object> paraMap) {
        try {
            String result = mpmProjectPolymerizationService.delete(paraMap);
            return ResponseResult.ok(result);
        } catch (Exception ex) {
			ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("删除单据出错，请联系管理员!");
        }
    }

    /**
     * @Title: submit
     * @description:提交审批
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月30日下午7:29:54
     * @modify:
     */
    @PutMapping(value = "/submit")
    public ResponseResult<Object> submit(@RequestBody(required = false) Map<String, Object> paraMap) {
        ResponseResult responseResult = mpmProjectController.submit(paraMap);
        return responseResult;
    }

    /**
     * @Title: delApproval
     * @description:取消审批
     * @param paraMap
     * @return
     * @author: lanjianwei
     * @date: 2020年12月30日下午7:38:59
     * @modify:
     */
    @DeleteMapping(value = "/delApproval")
    public ResponseResult<Object> delApproval(@RequestBody(required = false) Map<String, Object> paraMap) {
        String id = ConvertUtil.convertToString(paraMap.get("id"));
        mpmProjectService.executeDelApprove(id, this.getUserBean());
        return ResponseResult.ok("取消审批成功");
    }

    /**
     * @description: 项目查询模块列表
     * @param: paraMap
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     * @author Administrator
     * @date: 2021/1/15 9:52
     */
    @GetMapping("/projectQueryList")
    public ResponseResult<Object> projectQueryList(@RequestParam(required = false) Map<String, Object> paraMap) {
        try{
            Page page = mpmProjectPolymerizationService.listForQuery(paraMap,
                    existsSqlForQuery(paraMap, MpmProjectEntity.tableName, MpmProjectEntity.FieldProjectcode));
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ex.printStackTrace();
            return ResponseResult.error(ex.getMessage());
        }
    }

    /**
     * @description: 项目查询模块，列表数据权限sql组装
     * @param: paraMap
     * @param: tableName
     * @param: columnName
     * @return: java.lang.String
     * @author Administrator
     * @date: 2021/1/15 9:35
     */
    public String existsSqlForQuery(Map<String, Object> paraMap, String tableName, String columnName) {
        return this.mpmProjectPolymerizationService.existsSqlForQuery(paraMap, tableName, columnName);
    }

    /**
     * @description: 从线程本地副本中获取用户信息
     * @param:
     * @return: com.topscomm.tap.common.SessionUserBean
     * @author Administrator
     * @date: 2021/1/7 19:18
     */
    public SessionUserBean getUserBean() {
        return (SessionUserBean) ThreadLocalContext.sessionUserBeanThreadLocal.get();
    }
    /**
     * @description: 获取运营部门，当前存在三级部门，如果查不到所在部门的运营部门，
     * 则查询2所在部门的二级部门，
     * 如果所在二级部门也没有运营部门，则查询所在的一级部门运营部门
     * @param:
     * @return: com.topscomm.mis.pojo.MisDeptArchEntity
     * @author Administrator
     * @date: 2021/1/21 13:59
     */
    public MisDeptArchEntity getDeptArch(String deptCode) {
        String deptCodeThis = "";
        if (!"".equals(deptCode) && deptCode != null) {
            deptCodeThis = deptCode;
        } else {
            deptCodeThis = this.getUserBean().getDept().getCode();
        }
        StringBufferProxy sqlForDeptArch = new StringBufferProxy();
        sqlForDeptArch.appendSingle(" EXISTS (SELECT DEPTARCHCODE FROM MISDEPTARCHRELATED WHERE MISDEPTARCH.CODE = MISDEPTARCHRELATED.DEPTARCHCODE AND MISDEPTARCHRELATED.DEPTCODE = '{0}')", deptCodeThis);
        // 查询所在部门的运营部门
        List<Map<String, Object>> listDeptArchRelate = new ArrayList<>();
        listDeptArchRelate = misDeptArchService.queryMapByWhere(sqlForDeptArch.toString());
        if (listDeptArchRelate.size() == 0) {
            // 如果没有查询到所在运营部门则查询二级部门
            sqlForDeptArch.clear();
            // 所在部门未维护运营部门，查询所在二级部门的运营部门
            // 注意区分分公司
            if (deptCodeThis.contains("_")) {//分公司逻辑
                // 截取“_”后的字符串，如果是分公司则截取_后的六位
                String subDeptCode = deptCodeThis.substring(deptCodeThis.indexOf("_") + 1);
                if (subDeptCode.length() > 6) {
                    String deptCodeFix = deptCodeThis.substring(0, deptCodeThis.indexOf("_") + 1);
                    sqlForDeptArch.appendSingle(" EXISTS (SELECT DEPTARCHCODE FROM MISDEPTARCHRELATED WHERE MISDEPTARCH.CODE = MISDEPTARCHRELATED.DEPTARCHCODE AND MISDEPTARCHRELATED.DEPTCODE = '{0}')", deptCodeFix + subDeptCode.substring(0, 6));
                }
            } else {
                // 如果不是分公司则截取前6位
                if (deptCodeThis.length() > 6) {
                    String subDeptCode = deptCodeThis.substring(0, 6);
                    sqlForDeptArch.clear();
                    sqlForDeptArch.appendSingle(" EXISTS (SELECT DEPTARCHCODE FROM MISDEPTARCHRELATED WHERE MISDEPTARCH.CODE = MISDEPTARCHRELATED.DEPTARCHCODE AND MISDEPTARCHRELATED.DEPTCODE = '{0}')", subDeptCode);
                }
            }
            if (!sqlForDeptArch.toString().equals("")){
                // 查询二级部门的运营部门
                listDeptArchRelate = misDeptArchService.queryMapByWhere(sqlForDeptArch.toString());
            }
            // 如果所在二级部门也没有运营部门则查询所在一级部门的运营部门
            if (listDeptArchRelate.size() == 0) {
                if (deptCodeThis.contains("_")) {//分公司逻辑
                    // 截取“_”后的字符串，如果是分公司则截取_后的四位
                    String subDeptCode = deptCodeThis.substring(deptCodeThis.indexOf("_") + 1);
                    String deptCodeFix = deptCodeThis.substring(0, deptCodeThis.indexOf("_") + 1);
                    sqlForDeptArch.appendSingle(" EXISTS (SELECT DEPTARCHCODE FROM MISDEPTARCHRELATED WHERE MISDEPTARCH.CODE = MISDEPTARCHRELATED.DEPTARCHCODE AND MISDEPTARCHRELATED.DEPTCODE = '{0}')", deptCodeFix + subDeptCode.substring(0, 4));
                } else {
                    // 如果不是分公司则截取前4位
                    String subDeptCode = deptCodeThis.substring(0, 4);
                    sqlForDeptArch.clear();
                    sqlForDeptArch.appendSingle(" EXISTS (SELECT DEPTARCHCODE FROM MISDEPTARCHRELATED WHERE MISDEPTARCH.CODE = MISDEPTARCHRELATED.DEPTARCHCODE AND MISDEPTARCHRELATED.DEPTCODE = '{0}')", subDeptCode);
                }
                //  查询一级部门的运营部门
                listDeptArchRelate = misDeptArchService.queryMapByWhere(sqlForDeptArch.toString());
            }
        }
        MisDeptArchEntity entity = new MisDeptArchEntity();
        if (listDeptArchRelate.size() == 1) {
            entity.convertFromMap(listDeptArchRelate.get(0));
            return entity;
        } else {
            List<CboDeptEntity> cboDeptEntities = cboDeptService.queryByWhere("code = '" + deptCodeThis + "'");
            StringBufferProxy messageStr = new StringBufferProxy();
            messageStr.appendSingle("法人部门:{0}【{1}】,对应的运营部门配置错误，请联系管理员。",cboDeptEntities.get(0).getName(),cboDeptEntities.get(0).getCode());
            throw new MessageException(messageStr.toString());
        }
    }

    /**
     * 导出大数据量的excel
     *
     * @description:
     * @param paraMap
     * @param request
     * @param response
     * @author: donghang
     * @date: 2020年5月25日下午2:55:11
     * @modify:
     */
    @PostMapping(value = "/exportBigExcel")
    public ResponseResult<Object> exportBigExcel(@RequestHeader(value = "Authorization") String token, @RequestBody(required = false) Map<String, Object> paraMap,
                                                 HttpServletRequest request, HttpServletResponse response) {
        //导出参数设定
        String cols = ConvertUtil.convertToString(paraMap.get("cols"));
        String whereSql = existsSqlForQuery(paraMap, MpmProjectEntity.tableName, MpmProjectEntity.FieldProjectcode);
        String fileName = ConvertUtil.convertToString(paraMap.get("fileName"));
        fileName = fileName.length() == 0 ? "导出数据.xlsx" : fileName + ".xlsx";
        try {
            //获得导出数据，一次导出所有页面的数据
            paraMap.put("notQueryForPageFlag", true);
            paraMap.put("meetToken", token);
            Page page = mpmProjectPolymerizationService.listForQuery(paraMap, whereSql);
            // 导出数据
            exportExcelXlsx(response, ResponseResult.ok(page), fileName, cols);
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    /**
     * 导出excel
     *
     * @description:
     * @param fileName
     * @param columns
     * @return
     * @author: donghang
     * @date: 2020年5月26日上午10:52:17
     * @modify:
     */
    @SuppressWarnings("rawtypes")
    private void exportExcelXlsx(HttpServletResponse response, ResponseResult<Object> responseResult, String fileName,
            String columns) throws Exception {
        if (ConvertUtil.convertToString(fileName).length() == 0) {
            fileName = "导出数据表.xlsx";
        }
        if (!ConvertUtil.convertToString(fileName).contains("xlsx")) {
            fileName = fileName + ".xlsx";
        }
        InputStream inputStream = null;
        try {
            //设定最大导出行
            Page result = (Page) responseResult.getResult();
            List<Map<String, Object>>  dataList =(List<Map<String, Object>>) result.getDataList();
            int totalCount =  dataList.size();
            int exportMaxCount = ParameterCache.getIntValue("CboExcelInDirectMaxCount", 1000000);
            if (totalCount > exportMaxCount) {
                throw new BusinessException("最大允许导出" + exportMaxCount + "行");
            }
            // 是否直接相应，数量少的直接相应
            int exportDirectMaxCount = ParameterCache.getIntValue("CboExcelDirectMaxCount", 20000);
            boolean isResponse = totalCount < exportDirectMaxCount ? true : false;
            if (isResponse == true) {
                //重新填充返回数据，将含有多个机会点的数据扩充至多条，并合并单元格
//              parseChanceClueData(dataList);
                //合并单元格导出
                inputStream = exportExeclXlsx(dataList, columns.split(","));
            }
            // 文件流直接相应
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("istaskexport", "0");
            // 需要添加否则前台得不到istaskexport
            response.setHeader("Access-Control-Expose-Headers", "istaskexport");
            response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "FileName");
            OutputStream outStream = response.getOutputStream();
            byte[] bytes = new byte[2048];
            int len;
            if (inputStream != null) {
                while ((len = inputStream.read(bytes)) != -1) {
                    outStream.write(bytes, 0, len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 处理导出数据
     * @param dataList
     */
//    private void   parseChanceClueData(List<Map<String, Object>>  dataList){
//        //新建临时数组，存储临时数据
//        List<Map<String, Object>> mergedList = new ArrayList<>();
//        if(CollectionUtil.isEmpty(dataList)){
//            return;
//        }
//        //遍历查询结果，处理多机会点情况
//            for(int i = 0; i < dataList.size(); i++) {
//                List<Map<String, Object>> tempList = new ArrayList<>();
//                String chanceStr = String.valueOf(dataList.get(i).get("chanceclue"));
//                //判空
//                if (StringUtil.isEmpty(chanceStr) && NULL.equals(chanceStr)) {
//                    return;
//                }
//                //获得机会点数组，大于一个则新建数据，以便合并单元格
//                String[] chanceClues = chanceStr.split(",");
//                tempList.add(dataList.get(i));
//                if (chanceClues != null && chanceClues.length > 1) {
//                    //新建临时行
//                    for (int k = 1; k < chanceClues.length; k++) {
//                        Map<String, Object> chanceClueRow = new HashMap<>();
//                        chanceClueRow.put("chanceclue", chanceClues[k]);
//                        tempList.add(chanceClueRow);
//                    }
//                    tempList.get(0).put("chanceclue", chanceClues[0]);
//                    //设定一个chanceclueNum参数，确定改行有几个机会点
//                    tempList.get(0).put("chanceclueNum", chanceClues.length);
//                }
//                mergedList.addAll(tempList);
//            }
//            //将处理好的数据返回
//        dataList.clear();
//        dataList.addAll(mergedList);
//    }

    /**
     * 项目监管机会点合并单元格操作方法
     * @param listMap
     * @param columns
     * @return
     * @throws Exception
     */
    private InputStream exportExeclXlsx(List<Map<String, Object>> listMap, String[] columns) throws Exception {
        int rowIndex = 0;
        XSSFWorkbook wb = new XSSFWorkbook();
        // 表头格式
        XSSFCellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        XSSFFont font = wb.createFont();
        font.setFontName(I18nUtil.getMessage("tap.common.songti"));
        font.setBold(true);
        headCellStyle.setFont(font);
        // 表行格式
        XSSFCellStyle lineCellStyle = wb.createCellStyle();
        lineCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        lineCellStyle.setAlignment(HorizontalAlignment.CENTER);
        lineCellStyle.setBorderLeft(BorderStyle.THIN);
        lineCellStyle.setBorderRight(BorderStyle.THIN);
        lineCellStyle.setBorderTop(BorderStyle.THIN);
        lineCellStyle.setBorderBottom(BorderStyle.THIN);
        lineCellStyle.setWrapText(true);
        // 表头
        XSSFSheet sheet = wb.createSheet("sheet1");
        XSSFRow row = sheet.createRow(rowIndex++);
        int changePointCol = 6;
        for (int i = 0; i < columns.length; i++) {
            //获取机会点列值
            if(columns[i].equals("chanceclue;机会点")){
                changePointCol = i;
            }
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(headCellStyle);
            String[] kv = columns[i].split(";");
            String value = kv[1];
            cell.setCellValue(value);
        }
        // 表行
        for (Map<String, Object> data : listMap) {
            //合并单元格
            if(!NULL.equals(String.valueOf(data.get("chanceclueNum"))) && !StringUtils.isBlank(String.valueOf(data.get("chanceclueNum")))){
                int redRow =Integer.parseInt(String.valueOf(data.get("chanceclueNum")));
                //确定合并行类数
                int rowInfo = rowIndex + redRow;
                for(int i = 0;i<columns.length;i++) {
                    //机会点列不合并
                    if(i != changePointCol){
                        sheet.addMergedRegion(new CellRangeAddress(rowIndex , rowInfo-1, i, i));
                    }
                }

            }
            row = sheet.createRow(rowIndex++);
            for (int i = 0; i < columns.length; i++) {
                String[] kv = columns[i].split(";");
                String key = kv[0];
                XSSFCell cell = row.createCell(i);
                String value = "";
                if (!StringUtil.isEmpty(key) && data.get(key) != null) {
                    if (data.get(key) instanceof Date) {
                        value = DateUtil.dateToString((Date) data.get(key));
                        cell.setCellValue(value);
                    } else if (data.get(key) instanceof BigDecimal || data.get(key) instanceof Double
                            || data.get(key) instanceof Float) {
                        Double d = ConvertUtil.convertToDouble(data.get(key));
                        cell.setCellValue(d);
                    } else {
                        value = data.get(key).toString();
                        cell.setCellValue(value);
                    }
                }
                cell.setCellStyle(lineCellStyle);
            }
        }
        // 自适应列
        int columnCount = columns.length;
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
            int colWidth = sheet.getColumnWidth(i) * 17 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, colWidth);
            } else {
                sheet.setColumnWidth(i, 6000);
            }
        }
        // 生成流
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        InputStream is = null;
        try {
            wb.write(os);
            os.flush();
            byte[] content = os.toByteArray();
            is = new ByteArrayInputStream(content);
        } catch (Exception ex) {
            if (os != null) {
                os.close();
                os = null;
            }
            if (wb != null) {
                wb.close();
            }
        }
        return is;
    }

    /**
     * @author: chengengwei
     * @date: 2021-09-03
     * @description: 查询项目归档、立项归档列表
     * @modify:
     */
    @GetMapping("/projectFileList")
    public ResponseResult<Object> projectFileList(@RequestParam(required = false) Map<String, Object> paraMap) {
        // 查询结项的了记录，公开的项目也展示出来
        StringBufferProxy whereSql = new StringBufferProxy();
        // 是否公开作为and条件拼接
        whereSql.appendSingle(" {0} in ('2', '8') and {1} = '1' ", MpmProjectEntity.FieldProjectstate,
                MpmProjectEntity.FieldOpen);
        // 搜索条件sql拼接
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                whereSql.appendSingle(" and mpmproject.projectname like '%{0}%' ", value);
        }
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                whereSql.appendSingle(" and mpmproject.projectlevel in ({0})", value);
        }
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                whereSql.appendSingle(" and (mpmproject.projectmanager like '%{0}%' or mpmproject.projectmanagercode like '%{0}%') ", value);
        }
        Page page = mpmProjectPolymerizationService.listForQuery(paraMap, whereSql.toString());
        return ResponseResult.ok(page);
    }
    /**
     * @author: houyuhang
     * @date: 2021-11-08
     * @description: 获取任命超期项目
     * @modify:
     */
    @GetMapping("/queryAppointmentOverDate")
    public ResponseResult<Object> queryAppointmentOverDate(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            String whereSql = this.spellListSqlForProject(paraMap);
            Page page = mpmProjectPolymerizationService.queryAppointmentOverDate(paraMap,whereSql);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("加载列表页面出错，请联系管理员!");
        }
    }
    /**
     * @author: houyuhang
     * @date: 2021-11-08
     * @description: 获取策划超期项目
     * @modify:
     */
    @GetMapping("/queryPlanOverDate")
    public ResponseResult<Object> queryPlanOverDate(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            String whereSql = this.spellListSqlForProject(paraMap);
            Page page = mpmProjectPolymerizationService.queryPlanOverDate(paraMap,whereSql);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ErrorLogUtil.writeLog(ex);
            logger.info(ex.getMessage());
            return ResponseResult.error("加载列表页面出错，请联系管理员!");
        }
    }
    /**
     * @author: houyuhang
     * @date: 2021-12-21
     * @description:  首页信息概览超期项目、任命、策划和任命策划待办的查询条件sql拼接
     * @modify:
     */
    public String spellListSqlForProject(Map<String, Object> paraMap) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" mpmproject.enabled=1 ");
        // 项目名称
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectname like '%{0}%' ", value);
        }
        // 项目等级
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectlevel in ({0})", value);
        }
        // 项目经理
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and (mpmproject.projectmanager like '%{0}%' or mpmproject.projectmanagercode like '%{0}%') ", value);
        }
        // 项目异常状态
        if (paraMap.containsKey("qry_abnormalstate")) {
            String value = paraMap.get("qry_abnormalstate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle(" and abnormalstate in ({0})", value);
            }
        }
        // 项目状态
        if (paraMap.containsKey("qry_projectstate")) {
            String value = paraMap.get("qry_projectstate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.projectstate in ({0})", value);
        }
        // 项目所属省份
        if (paraMap.containsKey("qry_deptcommoncode")) {
            String value = paraMap.get("qry_deptcommoncode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and mpmproject.deptcommoncode in ({0}) ", value);
        }
        // qry_permission org公司，inChargeMe我负责的，participate我参与的
        if (paraMap.containsKey("qry_permission")) {
            String value = (String) paraMap.get("qry_permission");
            sql.appendSingle("and " + mpmProjectService.whereExistsSqlForIndex("MpmProject", "projectcode", value));
        }
        // 任命、策划超期/待办字符串拼接
        if(paraMap.containsKey("qry_overdue")) {
            if(ConvertUtil.convertToInt(paraMap.get("qry_overdue")) == 1) {
                if(ConvertUtil.convertToString(paraMap.get("qry_category")).equals("appointment")) {
                    // 任命超期拼接字符串，项目立项完成时间不为空，但项目任命不存在或者只存在一条未审批完成的任命
                    sql.appendSingle(" AND MPMPROJECT.FINISHDATE IS NOT NULL AND (MPMPROJECTAPPOINTMENT.ID IS NULL OR (MPMPROJECTAPPOINTMENT.STATE < 9 " +
                            " AND NOT EXISTS( SELECT ID FROM MPMPROJECTAPPOINTMENT MPMAPP WHERE MPMAPP.PROJECTCODE = MPMPROJECTAPPOINTMENT.PROJECTCODE" +
                            " AND MPMAPP.ID != MPMPROJECTAPPOINTMENT.ID)))");
                } else if(ConvertUtil.convertToString(paraMap.get("qry_category")).equals("plan")) {
                    // 策划超期拼接字符串,项目任命签署时间不为空，且为首次任命，但项目策划不存在或者只存在一条未审批完成的策划
                    sql.appendSingle(" AND MPMPROJECTAPPOINTMENT.SIGNDATE IS NOT NULL AND NOT EXISTS " +
                            " (SELECT ID FROM MPMPROJECTAPPOINTMENT MPMAPP WHERE MPMAPP.PROJECTCODE = MPMPROJECTAPPOINTMENT.PROJECTCODE " +
                            " AND MPMAPP.SIGNDATE < MPMPROJECTAPPOINTMENT.SIGNDATE) AND (MPMPROJECTPLAN.ID IS NULL" +
                            " OR (MPMPROJECTPLAN.STATE < 9 AND NOT EXISTS( SELECT ID FROM MPMPROJECTPLAN PLAN WHERE" +
                            " PLAN.PROJECTCODE = MPMPROJECTPLAN.PROJECTCODE AND PLAN.ID != MPMPROJECTPLAN.ID)))");
                } else {
                    throw new ServiceException("超期数据请求失败，请联系管理员");
                }
            } else if(ConvertUtil.convertToInt(paraMap.get("qry_overdue")) == 0) {
                if(ConvertUtil.convertToString(paraMap.get("qry_category")).equals("appointment")) {
                    // 任命待办拼接字符串，项目立项完成时间不为空，但项目任命不存在或者只存在一条未提交审批的任命
                    sql.appendSingle(" AND MPMPROJECT.FINISHDATE IS NOT NULL AND (MPMPROJECTAPPOINTMENT.ID IS NULL OR (MPMPROJECTAPPOINTMENT.STATE = 0 " +
                            " AND NOT EXISTS( SELECT ID FROM MPMPROJECTAPPOINTMENT MPMAPP WHERE MPMAPP.PROJECTCODE = MPMPROJECTAPPOINTMENT.PROJECTCODE" +
                            " AND MPMAPP.ID != MPMPROJECTAPPOINTMENT.ID)))");
                } else if(ConvertUtil.convertToString(paraMap.get("qry_category")).equals("plan")) {
                    // 策划待办拼接字符串,项目任命签署时间不为空，且为首次任命，但项目策划不存在或者只存在一条未提交审批的策划
                    sql.appendSingle(" AND MPMPROJECTAPPOINTMENT.SIGNDATE IS NOT NULL AND NOT EXISTS " +
                            " (SELECT ID FROM MPMPROJECTAPPOINTMENT MPMAPP WHERE MPMAPP.PROJECTCODE = MPMPROJECTAPPOINTMENT.PROJECTCODE " +
                            " AND MPMAPP.SIGNDATE < MPMPROJECTAPPOINTMENT.SIGNDATE) AND (MPMPROJECTPLAN.ID IS NULL" +
                            " OR (MPMPROJECTPLAN.STATE = 0 AND NOT EXISTS( SELECT ID FROM MPMPROJECTPLAN PLAN WHERE" +
                            " PLAN.PROJECTCODE = MPMPROJECTPLAN.PROJECTCODE AND PLAN.ID != MPMPROJECTPLAN.ID)))");
                } else {
                    throw new ServiceException("待办数据请求失败，请联系管理员");
                }
            } else {
                throw new ServiceException("超期、待办数据请求失败，请联系管理员");
            }
        }
        return sql.toString();
    }

    /**
     * @description: 系统部监管模块list方法
     * @param: paraMap
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     * @author zhangjunyao
     * @date: 2022/2/18 9:52
     */
    @GetMapping("/listForSys")
    public ResponseResult<Object> listForSys(@RequestParam(required = false) Map<String, Object> paraMap) {
        try{
            String sql = spellListForSys(paraMap);
             Page page = mpmProjectPolymerizationService.listForSys(paraMap, sql);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ex.printStackTrace();
            return ResponseResult.error(ex.getMessage());
        }
    }

    /**
     * @author: zhangjunyao
     * @date: 2022-2-18
     * @description:  系统部监管模块的查询条件sql拼接
     * @modify:
     */
    public String spellListForSys(Map<String, Object> paraMap) {
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle(" {0}.enabled = 1 and {1}.enabled = 1 and {0}.{2} = '0' ",
                MpmProjectEntity.tableName, MpmProjectSaleEntity.tableName, MpmProjectEntity.FieldProjectlevel);
        // 采购模式筛选
        if (paraMap.containsKey("qry_purchasepattern")) {
            String value = paraMap.get("qry_purchasepattern").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and {0}.{1} not in ({2})", MpmProjectSaleEntity.tableName, MpmProjectSaleEntity.FieldPurchasepattern, value);
            }
        }
        // 根据督办情况筛选
        if (paraMap.containsKey("qry_supervisestate")) {
            String value = paraMap.get("qry_supervisestate").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and (");
                // 若有选择无督办筛选条件则先执行（3为无督办对应编号）
                if (value.contains("3")) {
                    sql.appendSingle(" ( NOT EXISTS ( SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                            " supervise.PROJECTCODE = {0}.PROJECTCODE ) ) ", MpmProjectEntity.tableName);
                    // 若除了选择无督办筛选情况还选择了其他选项，则要把字符串进行截取。
                    // 因为无督办情况代表督办表无当前项目的督办记录，其他选项都是有督办记录的。
                    // 如下截取无需遍历字符串，提高代码效率
                    if (value.length() > 1) {
                        sql.appendSingle(" or ");
                        // 因为字符串排列顺序不同，若无督办情况不是第一个选中的选项则进行第一种截取（例如"0,1,3", "2,3,1"等情况）
                        // 若第一个选中的是无督办情况则进行第二种截取。主要区别在于逗号的位置不同。（例如"3,1,0"等情况）
                        if (value.indexOf("3") != 0) {
                            value = value.replace(",3", "");
                        } else {
                            value = value.replace("3,", "");
                        }
                    }
                }
                // 筛选进行中、超期、已完成三个状态的督办情况
                if (!value.contains("3")) {
                    sql.appendSingle(" ( EXISTS ( SELECT ID FROM MPMPROJECTSUPERVISE supervise WHERE" +
                                    " supervise.PROJECTCODE = {0}.PROJECTCODE AND supervise.SUPERVISESTATE IN ({1}) ) ) ",
                            MpmProjectEntity.tableName, value);
                }
                sql.appendSingle(" ) ");
            }
        }
        if (paraMap.containsKey("startDateForFinisthDate") && paraMap.containsKey("endDaterFinisthDate")) {
            String startDateForFinisthDate = paraMap.get("startDateForFinisthDate").toString(),
                    endDaterFinisthDate = paraMap.get("endDaterFinisthDate").toString();
            if (!StringUtil.isEmpty(startDateForFinisthDate) && !StringUtil.isEmpty(endDaterFinisthDate)) {
                sql.appendSingle(" and {0}.{1} > {2} and {0}.{1} < {3} ", MpmProjectEntity.tableName, MpmProjectEntity.FieldFinishdate,
                        SQLAdapter.toDateShort(startDateForFinisthDate), SQLAdapter.toDateShort(endDaterFinisthDate));
            }
        }
        if (paraMap.containsKey("startDateForActualEndDate") && paraMap.containsKey("endDateForActualEndDate")) {
            String startDateForActualEndDate = paraMap.get("startDateForActualEndDate").toString(),
                    endDateForActualEndDate = paraMap.get("endDateForActualEndDate").toString();
            if (!StringUtil.isEmpty(startDateForActualEndDate) && !StringUtil.isEmpty(endDateForActualEndDate)) {
                sql.appendSingle(" and {0}.{1} > {2} and {0}.{1} < {3} ", MpmProjectEntity.tableName, MpmProjectEntity.FieldFinishdate,
                        SQLAdapter.toDateShort(startDateForActualEndDate), SQLAdapter.toDateShort(startDateForActualEndDate));
            }
        }
        // 挂网日期筛选
        if (paraMap.containsKey("startDateForBidBeginDate") && paraMap.containsKey("endDateForBidBeginDate")) {
            String startDateForBidBeginDate = paraMap.get("startDateForBidBeginDate").toString(),
                    endDateForBidBeginDate = paraMap.get("endDateForBidBeginDate").toString();
            sql.appendSingle(" and bidbegindate between {0} and {1} ", SQLAdapter.toDateShort(startDateForBidBeginDate), SQLAdapter.toDateShort(endDateForBidBeginDate));
        }
        // 开标日期筛选
        if (paraMap.containsKey("startDateForBidEndDate") && paraMap.containsKey("endDateForBidEndDate")) {
            String startDateForBidEndDate = paraMap.get("startDateForBidEndDate").toString(),
                    endDateForBidEndDate = paraMap.get("endDateForBidEndDate").toString();
            sql.appendSingle(" and bidbegindate between {0} and {1} ", SQLAdapter.toDateShort(startDateForBidEndDate), SQLAdapter.toDateShort(endDateForBidEndDate));
        }
        if (paraMap.containsKey("query_createdepart")) {
            String value = paraMap.get("query_createdepart").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.deptname  in ({1})", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_owner")) {
            String value = paraMap.get("qry_owner").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.owner like '%{1}%' ", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_sponsor")) {
            String value = paraMap.get("qry_sponsor").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.sponsor like '%{1}%' ", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_bidcycle")) {
            String value = paraMap.get("qry_bidcycle").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.bidcycle  in ('{1}')", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_abnormalstate")) {
            String value = paraMap.get("qry_abnormalstate").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.abnormalstate  in ({1})", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_decisionusername")) {
            String value = paraMap.get("qry_decisionusername").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and EXISTS (SELECT ID FROM MPMPROJECTTEAM WHERE MpmProject.PROJECTCODE = MPMPROJECTTEAM.PROJECTCODE AND (MPMPROJECTTEAM.USERCODE like '%{0}%' OR MPMPROJECTTEAM.USERNAME like '%{0}%') AND MPMPROJECTTEAM.STATE = 9  AND MPMPROJECTTEAM.ENABLED = 1) ", value);
        }
        if (paraMap.containsKey("qry_projectstate")) {
            String value = paraMap.get("qry_projectstate").toString();
            if (!StringUtil.isEmpty(value)){
                //因为该查询是两个字段合并查询，所以需要重新解析页面传值
                //解析页面传值，将数字全部去出
                Pattern pattern = Pattern.compile("[^0-9]");
                Matcher matcher = pattern.matcher(value);
                String temp = matcher.replaceAll("");
                StringBufferProxy projectstateValue = new StringBufferProxy();
                StringBufferProxy schedulestateValue = new StringBufferProxy();
                for (int i = 0; i < temp.length(); i++) {
                    String status = temp.substring(i, i + 1);
                    switch (status){
                        case "0": case "1": case "2": case "3": case "4": case "8":
                            projectstateValue.appendSingle("'{0}',",status);
                            break;
                        case "5": case "6": case "7":
                            schedulestateValue.appendSingle("'{0}',",status);
                            break;
                    }
                }
                if(StringUtils.isNotBlank(projectstateValue.toString()) &&  StringUtils.isBlank(schedulestateValue.toString())){
                    sql.appendSingle(" and {0}.projectstate  in ({1})  ", MpmProjectEntity.tableName,
                            projectstateValue.substring(0, projectstateValue.length() - 1));
                } else if(StringUtils.isBlank(projectstateValue.toString()) &&  StringUtils.isNotBlank(schedulestateValue.toString())){
                    sql.appendSingle(" and {0}.schedulestate  in ({1})  ", MpmProjectEntity.tableName,
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                } else {
                    sql.appendSingle(" and ({0}.projectstate  in ({1}) or MpmProject.schedulestate  in ({2}) ) ", MpmProjectEntity.tableName,
                            projectstateValue.substring(0, projectstateValue.length() - 1),
                            schedulestateValue.substring(0, schedulestateValue.length() - 1));
                }

            }
        }
        if (paraMap.containsKey("qry_projectcode")) {
            String value = paraMap.get("qry_projectcode").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.projectcode  = '{1}'", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_projectlevel")) {
            String value = paraMap.get("qry_projectlevel").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.projectlevel  in ({1}) ", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_projectname")) {
            String value = paraMap.get("qry_projectname").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and ({0}.projectname like '%{1}%' or {0}.projectcode = '{1}') ", MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_projectmanager")) {
            String value = paraMap.get("qry_projectmanager").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and ({0}.projectmanager like '%{1}%' or {0}.projectmanagercode like '%{1}%') ",
                        MpmProjectEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_projectsource")) {
            String value = paraMap.get("qry_projectsource").toString();
            if (!StringUtil.isEmpty(value))
                sql.appendSingle(" and {0}.projectsource in ({1}) ", MpmProjectSaleEntity.tableName, value);
        }
        if (paraMap.containsKey("qry_endtype")) {
            String value = paraMap.get("qry_endtype").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and {0}.endtype in ({1})", MpmProjectEntity.tableName, value);
            }
        }
        if (paraMap.containsKey("qry_comments")) {
            int value = ConvertUtil.convertToInt(paraMap.get("qry_comments"));
            if (MpmSystemConst.EnableEnum.Enabled == value) {
                sql.appendSingle("and MPMPROJECT.id in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from MPMCOMMENTS b left join COMMENTSPERMISSIONRECORD c on b.id = c.SOURCEID where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and instr(',' || c.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b left join MPMCOMMENTS c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' left join COMMENTSPERMISSIONRECORD d on d.SOURCEID = c.ID where a.PROJECTCODE = b.PROJECTCODE  and instr(',' || d.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0))", this.getUserBean().getUser().getCode());
            } else {
                sql.appendSingle("and MPMPROJECT.id not in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from MPMCOMMENTS b left join COMMENTSPERMISSIONRECORD c on b.id = c.SOURCEID where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and instr(',' || c.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b left join MPMCOMMENTS c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' left join COMMENTSPERMISSIONRECORD d on d.SOURCEID = c.ID where a.PROJECTCODE = b.PROJECTCODE  and instr(',' || d.AUTHORIZEUSERCODES || ',', ',' || '{0}' || ',') > 0))", this.getUserBean().getUser().getCode());
            }
        }
        if (paraMap.containsKey("qry_focus")) {
            int value = ConvertUtil.convertToInt(paraMap.get("qry_focus"));
            if (MpmSystemConst.EnableEnum.Enabled == value) {
                sql.appendSingle("and MPMPROJECT.id in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from BaseOperateRecord b  where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and b.usercode = '{0}' and b.operatetype = 'mark' \n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b inner join BaseOperateRecord c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' and c.usercode = '{0}' and c.operatetype = 'mark' WHERE  a.projectcode = b.projectcode))", this.getUserBean().getUser().getCode());
            } else {
                sql.appendSingle("and MPMPROJECT.id not in (select ID from MPMPROJECT a\n" +
                        "where exists ( select 1 from BaseOperateRecord b  where a.ID = b.SOURCEID and b.SOURCETYPE = 'MpmProject' and b.usercode = '{0}' and b.operatetype = 'mark'\n" +
                        "          ) union all select ID  from MPMPROJECT a\n" +
                        "where exists(select 1 from MPMPROJECTTASK b inner join BaseOperateRecord c on  b.id = c.SOURCEID and c.SOURCETYPE = 'MpmProjectTask' and c.usercode = '{0}' and c.operatetype = 'mark' WHERE  a.projectcode = b.projectcode))", this.getUserBean().getUser().getCode());
            }
        }
        if (paraMap.containsKey("qry_needsupport")) {
            String value = paraMap.get("qry_needsupport").toString();
            if (!StringUtil.isEmpty(value)) {
                List<Map<String, Object>> mpmSystemTasknameList = datacodeCache.getCodeListMap("MpmSystemTaskname", "", "sortcode");
                String taskName = mpmSystemTasknameList.stream().map(item -> "'" + ConvertUtil.convertToString(item.get("name")) + "'").collect(Collectors.joining(","));
                sql.appendSingle(" and exists (select 1 from mpmprojecttask task where task.projectcode = mpmproject.projectcode and needsupport = '{0}' and task.state = 9 and {1} is not null and {2} in ({3})) ", value, MpmProjectTaskEntity.FieldPost, MpmProjectTaskEntity.FieldTaskname, taskName);
            }
        }
        // 如果是通过hession远程调用则没有登录用户，手动拼接用户信息
        SessionUserBean sessionUserBean = null;
        if (paraMap.containsKey("noLoginUser")) {
            SecurityUserDetails userDetails = (SecurityUserDetails) userDetailsService.loadUserByUsername(ConvertUtil
                    .convertToString(paraMap.get("usercodefromrpc")));
            sessionUserBean = new SessionUserBean();
            sessionUserBean.setUser(userDetails.getUser());
            sessionUserBean.setOrg(userDetails.getOrg());
            sessionUserBean.setWorkOrg(userDetails.getWorkOrg());
            sessionUserBean.setDept(userDetails.getDept());
            sessionUserBean.setWorkDept(userDetails.getWorkDept());
            sessionUserBean.setRoleList(userDetails.getRoleList());
        } else {
            sessionUserBean = this.getUserBean();
        }
        // 管理员不控制权限
        if (sessionUserBean.getUser().getAdminlevel() < 1) {
            // 直接
            String dataPession = this.mpmProjectService.getDataPermission(TapSystemConst.TapOperationTypeEnum.Select, "", sessionUserBean);
            if (!StringUtil.isEmpty(dataPession))
            {
                sql.appendSingle(" and {0} ", dataPession);
            }
        }
        return sql.toString();
    }

    /**
     * @description: 导出项目明细excel
     * @author: duzehou
     * @date: 2022/8/30 17:12
     * @param: [paraMap, request, response]
     * @return: com.topscomm.pub.vo.ResponseResult<java.lang.Object>
     **/
    @PostMapping(value = "/exportTaskDetailExcel" )
    public ResponseResult<Object> exportTaskDetailExcel(@RequestBody Map<String,Object> paraMap,
                                                 HttpServletRequest request, HttpServletResponse response){
        //todo：权限控制，南网国网系统部可以使用

        //此处处理嵌套的cols
        try {
            BaseUtil.decodeSecureMap(paraMap);
            //从dataCodeCache中获取树形结构 cols名
            List<Map<String, Object>> cacheCodeList = datacodeCache.getCodeListMap("MpmProjectTaskDetailsExport", "", "sortcode");
            if (cacheCodeList.isEmpty()){
                throw new ServiceException("编码不存在");
            }
            //将List转换为JSONArray
            List<Object> jsonObjs = cacheCodeList.stream().map(m -> JSONObject.parse(ConvertUtil.convertToString(m.get("code")))).collect(Collectors.toList());
            JSONArray cols= JSONArray.parseArray(JSON.toJSONString(jsonObjs));
            String fileName = ConvertUtil.convertToString(paraMap.get("fileName"));
            fileName = fileName.length() == 0 ? "导出数据.xlsx" : fileName + ".xlsx";
            //获取导出数据
            List<MpmProjectTaskDetailsVO> dataList  = mpmProjectPolymerizationService.exportTaskDetailExcel(paraMap,getUserBean());
            ObjectMapper mapper = new ObjectMapper();
            //Convert Pojo to Map
            List<Map<String,Object>> mapList = new ArrayList<>();
            for (int i = 0; i < dataList.size(); i++) {
                mapList.add(mapper.convertValue(dataList.get(i),Map.class)) ;
            }
            formatHelper(mapList);
            InputStream inputStream = OfficeOperateUtilExpand.exportExeclXlsxWithMergeHeader(mapList,cols);
            this.SetHeader(response, fileName);
            OutputStream outStream = response.getOutputStream();
            byte[] bytes = new byte[2048];
            int len;
            while ((len = inputStream.read(bytes)) != -1) {
                outStream.write(bytes, 0, len);
            }
            inputStream.close();
            //此处return null 因为如果响应体为正常json串 application contenttype 会被覆盖为json 导致导出文件破损
            return null;
        }catch (Exception e){
            throw new ServiceException(e);
        }
    }
    /**
     * @description: 处理报表导出格式  处理金额
     * @author xinfushuo
     * @param
     * @throws
     * @return
     * @date 2022/7/28
     * @modify
     */
    public void formatHelper(List<Map<String,Object>> mapList){
//        for (Map<String,Object> map : mapList){
//            for (Map.Entry<String,Object> entry :map.entrySet()){
//                if (entry.getKey().contains("money")){
//                    BigDecimal value = ConvertUtil.convertToBigDecimal(entry.getValue());
//                    entry.setValue(value.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
//                }
//            }
//        }
    }
    /**
     * @description: setHeader for Excel 导出
     * @author xinfushuo
     * @param  fileName 文件命名
     * @return
     * @date 2022/4/20
     * @modify
     */
    private HttpServletResponse SetHeader(HttpServletResponse response,String fileName)  {
        try{
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("istaskexport", "0");
            // 需要添加否则前台得不到istaskexport
            response.setHeader("Access-Control-Expose-Headers", "istaskexport");
            response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8"));
            response.setHeader("Access-Control-Expose-Headers", "FileName");
            return response;
        }catch (Exception e ){
            throw new ServiceException(e);
        }
    }
    /**
     * @description: fds系统获取人员能查看的销售项目数据
     * @author houyuhang
     * @date 2022/12/21
     * @modify
     */
    @GetMapping("/listForFMS")
    public ResponseResult<Object> listForFMS(@RequestParam(required = false) Map<String, Object> paraMap) {
        try {
            // 校验FMS系统传递key值
            String fmsToken = ParameterCache.getValue("FmsTokenProject");
            if (!fmsToken.equals(ConvertUtil.convertToString(paraMap.get("appKey")))) {
                throw new ServiceException("接口校验失败，请联系管理员！");
            }
            String sql = spellListSqlForFDS(paraMap);
            Page page = mpmProjectPolymerizationService.listForFMS(paraMap, sql);
            return ResponseResult.ok(page);
        } catch (Exception ex) {
            ex.printStackTrace();
            return ResponseResult.error(ex.getMessage());
        }
    }
    /**
     * @description: 拼接FDS的SQL
     * @author houyuhang
     * @date 2022/12/21
     * @modify
     */
    public String spellListSqlForFDS(Map<String, Object> paraMap) {
        StringBufferProxy sql = new StringBufferProxy();
        // 查询已审批完成且没有结项的项目
        sql.appendSingle(" {0}.enabled = 1 and projectstate in ({1}) ", MpmProjectEntity.tableName, MpmSystemConst.ExecuteStateCode);
        // 项目名称
        if (paraMap.containsKey("qry_name")) {
            String value = paraMap.get("qry_name").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and {0}.projectname like '%{1}%' ", MpmProjectEntity.tableName, value);
            }
        }
        // 项目编号
        if (paraMap.containsKey("qry_code")) {
            String value = paraMap.get("qry_code").toString();
            if (!StringUtil.isEmpty(value)) {
                sql.appendSingle("and {0}.projectcode like '{1}%' ", MpmProjectEntity.tableName, value);
            }
        }
        SessionUserBean userBean = mpmProjectPolymerizationService.querySessionUserBean(ConvertUtil.convertToString(paraMap.get("qry_usercode")));
        // 用户数据权限
        if (userBean.getUser().getAdminlevel() < 1) {
            String value = userBean.getUser().getCode();
            sql.appendSingle(" and (EXISTS (SELECT PROJECTCODE FROM MPMPROJECTSTAKEHOLDER STACKHOLDER WHERE STACKHOLDER.enabled = '1' and STACKHOLDER.USERCODE = '{0}' AND {1}.{2} = STACKHOLDER.PROJECTCODE) or projectmanagercode = '{0}' or usercode = '{0}' ",
                    value, MpmProjectEntity.tableName, MpmProjectEntity.FieldProjectcode);
            // 根据用户Code在deptExtraInfo表中找到自己作为省总或者财总的省份
            List<String> deptCommonCodes = mmsDeptExtraInfoService.getDeptCommonCodes(value);
            String commonCodes = StringUtils.join(deptCommonCodes.toArray(), ",");
            if (!ObjectUtil.isEmpty(commonCodes)){
                sql.appendSingle(" OR mpmproject.deptcommoncode IN ({0})", commonCodes);
            }
            // 拼接数据权限
            String dataPession = mpmProjectService.getDataPermission(TapSystemConst.TapOperationTypeEnum.Select, "", userBean);
            if (StringUtil.isEmpty(dataPession)) {
                sql.appendSingle(" ) ");
            } else {
                sql.appendSingle(" or {0}) ", dataPession);
            }
        }
        return sql.toString();
    }
}
